	USE [Robot]
--============================================================
go--1
--============================================================
create  PROCEDURE [dbo].[_sptblCurrencyInOpicsGet]
	@BuyCCY varchar(3),
	@SellCCY varchar(3)
AS

SELECT
	[ID],
	[BuyCurrency],
	[SellCurrency],
	[Status]
FROM
	[dbo].[tblCurrencyInOpics]
WHERE
	[BuyCurrency] = @BuyCCY and
	[SellCurrency]= @SellCCY and
	[Status] = 1
--============================================================
go --2
--============================================================
create PROCEDURE [dbo]._sptblConstantGetListByParaName
	@ParaName varchar(128)
AS

select * 

from tblConstant cont, tblParameter para 

where para.ParameterName = @ParaName and para.ID = cont.ParameterID

--============================================================
go --3
--============================================================

 create PROCEDURE [dbo].[_sptblFileUploadGetByFileName]
	@FileName varchar(128), @Peoplewise varchar(7)
AS

SELECT distinct
	[ID],
	[FileName],
	[UploadDate],
	[UploadBy],
	[Path],
	[FileType]
FROM
	[dbo].[tblFileUpload]
WHERE
	[FileName] = @FileName
and [UploadBy] = @Peoplewise
--============================================================
go --4
--============================================================
 create  PROCEDURE [dbo].[_sptblGetPDFFileByCreatedDate]
	@FromDate datetime, @ToDate datetime, @Peoplewise varchar(7)
AS

SELECT
	[ID],
	[FileName],
	[UploadDate],
	[UploadBy],
	[Path],
	[FileType]
FROM
	[dbo].[tblFileUpload]
WHERE
UploadDate between @FromDate and @ToDate
and [FileName] like '%.pdf'
and [UploadBy] = @Peoplewise

--============================================================
go --5
--============================================================
 create PROCEDURE [dbo].[_sptblGetRateFileByCreatedDate]
	@FromDate datetime,@ToDate datetime, @Peoplewise varchar(7)
AS

SELECT
	[ID],
	[FileName],
	[UploadDate],
	[UploadBy],
	[Path],
	[FileType]
FROM
	[dbo].[tblFileUpload]
WHERE
UploadDate between @FromDate and @ToDate
and [FileName] like '%.xls'
and [UploadBy] = @Peoplewise
--============================================================
go --6 Get transaction by created date
--============================================================
create procedure _sptblImportTransactionGetByCreatedDate
@FromDate Datetime, @ToDate datetime, @Peoplewise varchar(7)

as

select 
	[ID],
	[ValueDate],
	[BuyCurrency],
	[SellCurrency],
	[Createddate],
	[Branch],
	[CusRate],
	[BuyAmount],
	[SellAmount],
	[CreatedBy],
	[CustomerName],
	[Status],
	[Exception],
	[Rule1],
	[Rule2],
	[Rule3],
	[Rule4],
	[Rule5]
from tblImportTransaction 

where Createddate between @FromDate and @ToDate
and [CreatedBy] = @Peoplewise
--============================================================
go --7 Get transaction submitted by created date
--============================================================
Create procedure [dbo].[_sptblImportTransactionGetSubmit]
@FromDate Datetime, @ToDate datetime

as

select 
	[ID],
	[ValueDate],
	[BuyCurrency],
	[CreatedBy],
	[SellCurrency],
	[Branch],
	[CusRate],
	[BuyAmount],
	[SellAmount],
	[CreatedBy],
	[CustomerName],
	[Status],
	[Exception],
	[Rule1],
	[Rule2],
	[Rule3],
	[Rule4],
	[Rule5]
from tblImportTransaction 

where[CreatedDate] between @FromDate and @ToDate
and Status = 1
--============================================================
go --7 Get transaction submitted by created date
--============================================================
create procedure _sptblImportTransactionGetByCreatedDateStatus
@FromDate Datetime, @ToDate datetime,@Status int, @Peoplewise varchar(7)

as

select 
	[ID],
	[ValueDate],
	[CreatedDate],
	[BuyCurrency],
	[SellCurrency],
	[Branch],
	[CusRate],
	[BuyAmount],
	[SellAmount],
	[CreatedBy],
	[CustomerName],
	[Status],
	[Exception],
	[Command],
	[Rule1],
	[Rule2],
	[Rule3],
	[Rule4],
	[Rule5]
from tblImportTransaction 

where [CreatedDate] between @FromDate and @ToDate
and Status = @Status
and [CreatedBy] = @Peoplewise
--============================================================
go --8
--============================================================
create procedure _sptblImportTransactionGetByFileID
@FileID uniqueidentifier

as

SELECT
	[ID],
	[ValueDate],
	[BuyCurrency],
	[SellCurrency],
	[CusRate],
	[BuyAmount],
	[SellAmount],
	[Branch],
	[Rule1],
	[Rule2],
	[Rule3],
	[Rule4],
	[Rule5],
	[FromFileID],
	[CreatedBy],
	[CustomerName],
	[CreatedDate],
	[ApprovedDate],
	[ApprovedBy],
	[Channel],
	[BatchNo],
	[CustMasterNo],
	[CusSeg],
	[SlNo],
	[CustCD],
	[TranReferenceNo],
	[Exception],
	[Status]
FROM
	[dbo].[tblImportTransaction]
WHERE [FromFileID] = @FileID
--============================================================
go --9 Get transaction submitted by created date
--============================================================
CREATE procedure [dbo].[_sptblImportTransactionUnSubmitGetByCreatedDate]
@FromDate Datetime, @ToDate datetime, @Peoplewise varchar(7)

as

select 
	[ID],
	[ValueDate],
	[CreatedDate],
	[BuyCurrency],
	[SellCurrency],
	[Branch],
	[CusRate],
	[BuyAmount],
	[SellAmount],
	[CreatedBy],
	[CustomerName],
	[Status],
	[Exception],
	[Command],
	[Rule1],
	[Rule2],
	[Rule3],
	[Rule4],
	[Rule5]
from tblImportTransaction 

where [CreatedDate] between @FromDate and @ToDate
and ( Status = 0 or Status = 2)
and [CreatedBy] = @Peoplewise

--============================================================
go --10 Update rule for transaction
--============================================================
Create PROCEDURE [dbo].[_sptblImportTransactionUpdateRule]
	@ID uniqueidentifier, 
	@Rule1 int, 
	@Rule2 int, 
	@Rule3 int, 
	@Rule4 int, 
	@Rule5 int, 
	@Exception varchar(256) 
AS

UPDATE [dbo].[tblImportTransaction] SET
	[Rule1] = @Rule1,
	[Rule2] = @Rule2,
	[Rule3] = @Rule3,
	[Rule4] = @Rule4,
	[Rule5] = @Rule5,
	[Exception] = @Exception
WHERE
	[ID] = @ID
--============================================================
go --11 Update importtransaction staus
--============================================================
Create PROCEDURE [dbo].[_sptblImportTransactionUpdateStatus]
	@ID uniqueidentifier, 
	@Status int
AS

UPDATE [dbo].[tblImportTransaction] SET
	[Status] = @Status
WHERE
	[ID] = @ID

--============================================================
go--12 delete all internal rate in today
--============================================================
CREATE PROCEDURE [dbo].[_sptblInternalRateDeleteAllOnDay]
@Peoplewise varchar(7)
AS

DELETE

FROM
	[dbo].[tblInternalRate]
WHERE
	[CreatedDate] = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))  
	and [CreatedBy] = @Peoplewise
--============================================================
go --13
--============================================================
CREATE PROCEDURE [dbo].[_sptblInternalRateGetByCurrency]
	@BuyCCY varchar(3), @SellCCY varchar(3)
AS

SELECT
	[ID],
	[BuyCCY],
	[FloorRate],
	[SellCCY],
	[CeilingRate],
	[InternalRate],
	[FromFileID],
	[CreatedDate],
	[CreatedBy]
FROM
	[dbo].[tblInternalRate]
WHERE
	[BuyCCY] = @BuyCCY and
	[SellCCY] = @SellCCY and
[CreatedDate] = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
--============================================================
go --14
--============================================================
create procedure _sptblInternalRateGetByFileID
@FileID uniqueidentifier

as

select 
	[ID],
	[BuyCCY],
	[FloorRate],
	[SellCCY],
	[CeilingRate],
	[InternalRate],
	[FromFileID],
	[CreatedDate],
	[CreatedBy] 

from tblInternalrate

where fromfileid = @FileID
--============================================================
go -- 15 get internal rate in today
--============================================================
create procedure [dbo].[_sptblInternalRateGetOnDay]

as

select 
	[ID],
	[BuyCCY],
	[FloorRate],
	[SellCCY],
	[CeilingRate],
	[InternalRate],
	[FromFileID],
	[CreatedDate],
	[CreatedBy]

from tblInternalrate

where createddate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))  

--============================================================
go --16
--============================================================
CREATE procedure [dbo].[_sptblOpicsTransactionGetByStatus]
@Status int

as

select 
	[ID],
	[ValueDate],
	[DealDate],
	[DealNo],
	[BuyCurrency],
	[SellCurrency],
	[CustomerRate],
	[BuyAmount],
	[SellAmount],
	[InternalRate],
	[Customer],
	[Porfolio],
	[Branch],
	[ImportTransactionID],
	[RunningDate],
	[Status]
FROM
	[dbo].[tblOpicsTransaction]
WHERE
Status = @Status
--============================================================
go 
--============================================================
